package com.bacel.nb.product.web.util;

import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.WritableFont;
import jxl.write.WritableCellFormat;
import jxl.write.Label;
import jxl.Workbook;


import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Map;
import java.util.List;

/**
 * Created by liyu on 16/12/12.
 */
public final class ExcelOperation {

    /**
     * 私有构造函数
     */
    private ExcelOperation() {

    }

    public static Integer createExcel(final HttpServletResponse response,
                                      final String excelTitle,
                                      final String sheetName,
                                      final String[] thead,
                                      final List<String[]> tbody) throws IOException, WriteException {
        OutputStream os = response.getOutputStream();
        if (null == os) {
            return -3;
        }
//        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("content-disposition","attachment;filename=" + URLEncoder.encode(excelTitle + ".xls","UTF-8"));
        //创建工作簿
        WritableWorkbook workbook = Workbook.createWorkbook(os);
        int count = 0;

        //创建新的一页
        String sheetTitle = sheetName;
        {
            WritableSheet sheet = workbook.createSheet(sheetTitle, count);
            {
                //构造表头
                sheet.mergeCells(0, 0, thead.length-1, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
                WritableFont bold = new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为16,采用黑体显示
                WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
                titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
                titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
                Label title = new Label(0,0,excelTitle,titleFormate);
                sheet.setRowView(0, 600, false);//设置第一行的高度
//                sheet.setColumnView(0,200);
//                sheet.setColumnView(1,200);
                sheet.addCell(title);
            }


            //创建要显示的具体内容
            WritableFont color = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD);//选择字体
            WritableFont cfont = new WritableFont(WritableFont.ARIAL,10);//选择字体
            // color.setColour(Colour.GOLD);//设置字体颜色为金黄色
            WritableCellFormat colorFormat = new WritableCellFormat(color);
            WritableCellFormat cFormat = new WritableCellFormat(cfont);
            colorFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
            cFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中

            int tcount = 0;

            for (String node: thead) {
                //System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
                Label formate = new Label(tcount,1,node,colorFormat);
                sheet.addCell(formate);
                sheet.setColumnView(tcount, node.length()*10);
                tcount++;
            }
            int line = 2;
            for(String[] row : tbody)
            {
                int ccount = 0;
                for (String entry : row) {
                    Label example = new Label(ccount,line,entry,cFormat);
                    sheet.addCell(example);
                    ccount++;
                }
                line++;
            }
        }
        //把创建的内容写入到输出流中，并关闭输出流
        workbook.write();
        workbook.close();
//        os.flush();
//        os.close();
        //os.close();
        return 0;
    }
}
